Welcome to Staging Areas for Data Warehouses. After watching this video, you will be able to: Describe what a data warehouse staging area is. Describe why a staging area may be used. Relate how a staging area is used as a
first step for integrating data sources. What is a data warehouse staging area? You can think of a staging area as an intermediate
storage area that is used for ETL processing. Thus, staging areas act as a bridge
between data sources and the target data warehouses, data marts, or other data repos. They are often transient, meaning that they are
erased after successfully running ETL workflows. However, many architectures hold data
for archival or troubleshooting purposes. They are also useful for monitoring
and optimizing your ETL workflows. Staging areas can be implemented
in many ways, including: simple flat files, such as csv
files, stored in a directory, and managed using tools such as Bash or Python, or a set of SQL tables in a
relational database such as Db2, or a self-contained database instance within a data warehousing or business intelligence
platform such as Cognos Analytics. Let’s explore an example use case, to illustrate
a possible architecture for a Data Warehouse containing a Staging Area, which in turn
includes an associated Staging Database. Imagine the enterprise would
like to create a dedicated “Cost Accounting” Online
Analytical Processing system. The required data is managed in separate
Online Transaction Processing Systems within the enterprise, from the Payroll,
Sales, and Purchasing departments. From these siloed systems, the data is
extracted to individual Staging Tables, which are created in the Staging Database. Data from these tables is then transformed in the Staging Area using SQL to conform it to the
requirements of the Cost Accounting system. The conformed tables can now be
integrated, or joined, into a single table. The final phase is the loading phase, where the data is loaded into the
target cost-accounting system. A staging area can have many functions. Some typical ones include: Integration: Indeed, one of the
primary functions performed by a staging area is consolidation of
data from multiple source systems. Change detection: Staging areas can
be set up to manage extraction of new and modified data as needed. Scheduling: Individual tasks within an
ETL workflow can be scheduled to run in a specific sequence,
concurrently, and at certain times. There’s also: Data cleansing and validation. For example, you
can handle missing values and duplicated records. Aggregating data: You can use the
staging area to summarize data. For example, daily sales data
can be aggregated into weekly, monthly, or annual averages, prior
to loading into a reporting system. Normalizing data: To enforce consistency of
data types, or names of categories such as country and state codes in place of mixed naming
conventions such as “Mont,” “MA,” or “Montana.” A staging area is a separate location, where
data from source systems is extracted to. The extraction step therefore
decouples operations such as validation, cleansing and other
processes from the source environment. This helps to minimize any risk
of corrupting source-data systems, and simplifies ETL workflow
construction, operation, and maintenance. If any of the extracted data becomes
corrupted somehow, you can easily recover. In this video, you learned that: A staging area acts as a bridge between
data sources and the target system and are mainly used to integrate disparate
data sources in data warehouses. Staging areas can be implemented
quite simply as a set of flat files in a directory and managed with
scripts, or as tables in a database. Staging areas decouple data
processing from the source systems and thus help minimize risk of data corruption. Although they are often transient, staging areas can be held for
archiving or troubleshooting purposes.